using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections;
using System.Data.SqlClient;

/// <summary>
/// Summary description for UpdateInput
/// </summary>
public class UpdateInput
{
    public UpdateInput()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    public ArrayList UpdateOperation(DataSet dsePageInfo, DataSet dseInputInfo)
    {
        string strPageId = "";
        ArrayList arlCmdObj = new ArrayList();
        ArrayList arlPageCmdObj = null;
        DataTable dtaPageSet = dsePageInfo.Tables["PageSet"];
        DataTable dtaPage = dsePageInfo.Tables["Page"];
        DataTable dtaPageKeys = dsePageInfo.Tables["PageKeys"];
        DataTable dtaPageCtrl = dsePageInfo.Tables["PageCtrl"];
        DataTable dtaPageInputs;
        security objSec = new security();
        for (int i = 0; i < dtaPage.Rows.Count; i++)
        {
            if (!Convert.ToBoolean(objSec.getObjectRightsForUser(dtaPage.Rows[i]["object_id"].ToString(), "edit")))
                continue;
            strPageId = dtaPage.Rows[i]["page_id"].ToString();
            dtaPageInputs = dseInputInfo.Tables[strPageId];
            arlPageCmdObj = new ArrayList();
            if (dtaPageInputs.Rows.Count > 0)
            {
                arlPageCmdObj = UpdatePage(dtaPage, dtaPageCtrl, dseInputInfo.Tables[strPageId], dtaPageKeys, i);
            }

            for (int j = 0; j < arlPageCmdObj.Count; j++)
            {
                arlCmdObj.Add(arlPageCmdObj[j]);
            }
        }
        return arlCmdObj;
    }
    public string getDateValues(string strFldValue, bool convertToUniversal)
    {
        try
        {
            if (convertToUniversal)
                return DateTime.Parse(strFldValue, System.Globalization.CultureInfo.InvariantCulture).ToUniversalTime().ToString();
            if (HttpContext.Current.Request.QueryString["PK"] == null)
                return DateTime.Parse(strFldValue, System.Globalization.CultureInfo.InvariantCulture).ToUniversalTime().ToString();
            else
                return DateTime.Parse(strFldValue, System.Globalization.CultureInfo.InvariantCulture).ToString();
        }
        catch (Exception e)
        {
            return strFldValue;
        }
    }
  public ArrayList UpdatePage(DataTable dtaPage, DataTable dtaPageCtrl, DataTable dtaInputInfo, DataTable dtaPageKeys, int intPageSeq)
    {
        ArrayList arlReturn = new ArrayList();
        string strSql = "";
        string strFlds = "";
        string strFldName = "";
        string strFldValue = "";
        string strDataType = "";
        string strWhrCond = "";
        DataRow droPage = dtaPage.Rows[intPageSeq];
        string strPageId = droPage["page_id"].ToString();
        string sAF_ROW_ID = droPage["af_row_id"].ToString();
        DataRow[] droPageCtrl = dtaPageCtrl.Select("page_id='" + sAF_ROW_ID + "'");
        bool blnMultipleRecsPage = Convert.ToBoolean(droPage["multiple_recs_page"].ToString());
        if (blnMultipleRecsPage)
        {
            if ((HttpContext.Current.Request.Path.IndexOf("PageWizard.aspx") == -1))
            {
                arlReturn = UpdateMultipleRecsPage(dtaPage, dtaPageCtrl, dtaInputInfo, dtaPageKeys, intPageSeq);
            }
            else
            {
                arlReturn = UpdatePagewizard(dtaPage, dtaPageCtrl, dtaInputInfo, dtaPageKeys, intPageSeq); ;
            }
        }
        else
        {
            string strTableName = droPage["page_table_name"].ToString();
            SqlCommand cmdObj;
            SqlParameter spmObj;
            string strCtrlId = "";
            for (int i = 0; i < dtaInputInfo.Rows.Count; i++)
            {
                strFlds = "";
                cmdObj = new SqlCommand();
                for (int j = 0; j < droPageCtrl.Length; j++)
                {
                    spmObj = new SqlParameter();
                    //////strCtrlId = droPageCtrl[j]["control_id"].ToString();
                    /////j
                    strCtrlId = droPageCtrl[j]["control_attrib_id"].ToString();
                    strFldName = droPageCtrl[j]["control_attrib_id"].ToString();
                    strDataType = droPageCtrl[j]["control_attrib_type"].ToString();
                    strFldValue = dtaInputInfo.Rows[i][strCtrlId].ToString();
                    if (strFlds != "")
                    {
                        strFlds += ",";
                    }
                    strCtrlId = strCtrlId.Replace(" ", "#").Replace("'", "").Replace(":", "#");
                    strFlds += "[" + strFldName + "]=@" + strCtrlId;
                    strCtrlId = "@" + strCtrlId;
                    switch (strDataType.ToUpper())
                    {
                        case GlobalValues.Text:
                            if (strFldValue == "")
                                cmdObj.Parameters.Add(strCtrlId, SqlDbType.NVarChar ).Value = DBNull.Value;
                            else
                                cmdObj.Parameters.Add(strCtrlId, SqlDbType.NVarChar ).Value = strFldValue;
                            break;
                        case GlobalValues.Number:
                            if (strFldValue == "")
                                cmdObj.Parameters.Add(strCtrlId, SqlDbType.Int).Value = DBNull.Value  ;
                            else
                                cmdObj.Parameters.Add(strCtrlId, SqlDbType.Int).Value = strFldValue;
                            break;
                        case GlobalValues.Date:
                            if (strFldValue == "")
                                cmdObj.Parameters.Add(strCtrlId, SqlDbType.DateTime).Value = DBNull.Value;
                            else
                                cmdObj.Parameters.Add(strCtrlId, SqlDbType.DateTime).Value = strFldValue;
                            break;
                        case GlobalValues.DateTime:
                            if (strFldValue == "")
                                cmdObj.Parameters.Add(strFldName, SqlDbType.DateTime).Value = DBNull.Value;
                            else
                            {
                                cmdObj.Parameters.Add(strFldName, SqlDbType.DateTime).Value = strFldValue;
                                //////DynamicDisplayI DDI = new DynamicDisplayI();
                                //////string sUTC_Offset = DDI.getUserSetting("sy_usr", "time_zone", "sy_timezone", "utc_offset", "timezone_id");
                                //////if (sUTC_Offset == "" | sUTC_Offset == "0")
                                //////{
                                //////    //if (HttpContext.Current.Request.QueryString["PK"] == null)
                                //////    //    cmdObj.Parameters.Add(strFldName, SqlDbType.DateTime).Value = DateTime.Parse(strFldValue, System.Globalization.CultureInfo.InvariantCulture).ToUniversalTime().ToString();
                                //////    //else
                                //////    //    cmdObj.Parameters.Add(strFldName, SqlDbType.DateTime).Value = DateTime.Parse(strFldValue, System.Globalization.CultureInfo.InvariantCulture).ToString();
                                //////    cmdObj.Parameters.Add(strFldName, SqlDbType.DateTime).Value = getDateValues(strFldValue,false);
                                //////}
                                //////else
                                //////{
                                //////    //cmdObj.Parameters.Add(strFldName, SqlDbType.DateTime).Value = DateTime.Parse(strFldValue, System.Globalization.CultureInfo.InvariantCulture).ToUniversalTime().ToString();
                                //////    cmdObj.Parameters.Add(strFldName, SqlDbType.DateTime).Value = getDateValues(strFldValue, true);
                                //////}
                                //////DDI = null;
                            }
                            break;
                        case GlobalValues.Boolean:
                            if (strFldValue == "")
                                cmdObj.Parameters.Add(strCtrlId, SqlDbType.Bit).Value = DBNull.Value;
                            else if (Convert.ToBoolean(strFldValue))
                                cmdObj.Parameters.Add(strCtrlId, SqlDbType.Bit).Value = 1;
                            else 
                                cmdObj.Parameters.Add(strCtrlId, SqlDbType.Bit).Value = 0;
                            break;
                        case GlobalValues.Decimal:
                            if (strFldValue == "")
                                cmdObj.Parameters.Add(strCtrlId, SqlDbType.Decimal).Value = DBNull.Value;
                            else
                                cmdObj.Parameters.Add(strCtrlId, SqlDbType.Decimal).Value = strFldValue;
                            break;
                        case GlobalValues.Float:
                            if (strFldValue == "")
                                cmdObj.Parameters.Add(strCtrlId, SqlDbType.Float).Value = DBNull.Value;
                            else
                                cmdObj.Parameters.Add(strCtrlId, SqlDbType.Float).Value = strFldValue;
                            break;
                        default:
                            cmdObj.Parameters.Add(strCtrlId, SqlDbType.NVarChar).Value = strFldValue;
                            break;
                    }
                }
                strWhrCond = GetWhereCond(droPage, dtaPageKeys, droPageCtrl, dtaInputInfo.Rows[i]);
                strSql = "update " + strTableName + " set " + strFlds + " where " + strWhrCond;
                cmdObj.CommandText = strSql;
                arlReturn.Add(cmdObj);
                //getRelationUpdateCommands(ref dtaPage, ref dtaInputInfo, ref arlReturn);
                RelationOperation objRO = new RelationOperation();
                objRO.ApplyRelationOperation(ref arlReturn, HttpContext.Current.Request.QueryString["PK"], dtaPage, dtaInputInfo, "UPDATE");
                objRO = null;
            }
        }
        return arlReturn;
    }

    private string GetWhereCond(DataRow droPage, DataTable dtaPageKeys, DataRow[] droPageCtrl, DataRow droInputInfo)
    {
        string strWhCond = "";
        string strPageId = droPage["page_id"].ToString();
        string sAF_ROW_ID = droPage["af_row_id"].ToString();
        string strPageTblName = droPage["page_table_name"].ToString();
        DataRow[] droPageKeys = dtaPageKeys.Select("af_row_id='" + sAF_ROW_ID + "'");//, "page_key_seq");
        //DataRow[] droPageKeys = dtaPageKeys.Select("page_id='" + sAF_ROW_ID + "'");//, "page_key_seq");
        //string strKeyCol = "";
        //string strKeyControlId = "";
        //if (droPageKeys.Length > 0)
        //{
        //    for (int i = 0; i < droPageKeys.Length; i++)
        //    {
        //        strKeyCol = droPageKeys[i]["page_key"].ToString();
        //        for (int j = 0; j < droPageCtrl.Length; j++)
        //        {
        //            if (strKeyCol == droPageCtrl[j]["control_attrib_id"].ToString())
        //            {
        //                strKeyControlId = droPageCtrl[j]["control_attrib_id"].ToString();
        //                j = droPageCtrl.Length;
        //            }
        //        }
        //        if (strWhCond != "")
        //        {
        //            strWhCond += " and ";
        //        }
        //        strWhCond += " [" + strPageTblName + "].[" + strKeyCol + "] = '" + droInputInfo[strKeyControlId].ToString().Replace("'", "''") + "' ";
        //    }
        //}
        //else
        strWhCond = " [" + strPageTblName + "].[af_row_id]='" + droInputInfo["af_row_id"].ToString() + "'";
        return strWhCond;
    }

    private ArrayList UpdateMultipleRecsPage(DataTable dtaPage, DataTable dtaPageCtrl, DataTable dtaInputInfo, DataTable dtaPageKeys, int intPageSeq)
    {
        ArrayList arlReturn = new ArrayList();
        if (dtaInputInfo.Rows.Count == 0)
            return arlReturn;
        DataRow droPage = dtaPage.Rows[intPageSeq];
        DeleteInput DI = new DeleteInput();
        ArrayList arlDel = new ArrayList();
        string strPageKeyValues = "";
        PageCommonFunctions PCF = new PageCommonFunctions();
        LoadEditPage LEP = new LoadEditPage();
        string strKey = "";
        string sAF_ROW_ID = droPage["af_row_id"].ToString();
        string strPageId = droPage["page_id"].ToString();
        DataRow[] droPageCtrl = dtaPageCtrl.Select("page_id='" + sAF_ROW_ID + "'");

        DataRow[] droPageKeys = dtaPageKeys.Select("af_row_id='" + sAF_ROW_ID + "'");//, "page_key_seq");
        //DataRow[] droPageKeys = dtaPageKeys.Select("page_id='" + sAF_ROW_ID + "'");//, "page_key_seq");
        //strKey = "af_row_id";
        //for (int i = 0; i < droPageKeys.Length; i++)
        //{
        //string sTemp = LEP.getMasterDetailKey(droPage["page_table_name"].ToString());
        //if (sTemp != "")
        //{
        //    strKey = sTemp;
        //    droPageKeys[i]["page_key"] = sTemp;
        //}
        //else
        //    strKey = droPageKeys[i]["page_key"].ToString();
        /*if (PCF.IsParentPageKeyControl(strKey, dtaPage, dtaPageKeys, intPageSeq))
        {
            if (strPageKeyValues != "")
            {
                strPageKeyValues += "~";
            }*/
        strKey = "af_row_id";
        strPageKeyValues += PCF.FindInputValue(dtaInputInfo, sAF_ROW_ID, strKey, dtaPageCtrl);
        //}
        //}
        //if (dtaPageKeys.Rows.Count > 1)
        //    updateMasterKey(ref dtaInputInfo, strKey, strPageKeyValues);

        if (dtaInputInfo.Rows.Count > 1)
            arlDel = DI.DeleteMultipleRecords(dtaPage, droPageCtrl, strPageKeyValues, dtaPageKeys, intPageSeq, ref dtaInputInfo);
        else
            arlDel = DI.DeletePage(dtaPage, droPageCtrl, strPageKeyValues, dtaPageKeys, intPageSeq);
        for (int i = 0; i < arlDel.Count; i++)
        {
            arlReturn.Add(arlDel[i]);
        }
        InsertInput II = new InsertInput();
        ArrayList arlIns = II.InsertPage(droPage, droPageCtrl, dtaInputInfo);
        for (int i = 0; i < arlIns.Count; i++)
        {
            arlReturn.Add(arlIns[i]);
        }
        return arlReturn;
    }

    private ArrayList UpdatePagewizard(DataTable dtaPage, DataTable dtaPageCtrl, DataTable dtaInputInfo, DataTable dtaPageKeys, int intPageSeq)
    {
        ArrayList arlReturn = new ArrayList();
        DataRow droPage = dtaPage.Rows[intPageSeq];
        string sAF_ROW_ID = droPage["af_row_id"].ToString();
        DataRow[] droPageCtrl = dtaPageCtrl.Select("page_id='" + sAF_ROW_ID + "'");
        SqlCommand cmdObj;
        string strWhrCond = "";
        string strSql = "";

        switch (dtaInputInfo.TableName)
        {
            case "p_dobj_att":
                cmdObj = new SqlCommand();
                strWhrCond = "object_id='" + dtaInputInfo.Rows[0]["object_id"].ToString() + "'";
                strSql = "Delete from [sy_obj_att] where " + strWhrCond;
                cmdObj.CommandText = strSql;
                arlReturn.Add(cmdObj);
                cmdObj = new SqlCommand();
                strWhrCond = "object_id='" + dtaInputInfo.Rows[0]["object_id"].ToString() + "'";
                strSql = "Delete from [sy_obj_mas_detail] where " + strWhrCond;
                cmdObj.CommandText = strSql;
                arlReturn.Add(cmdObj);
                break;
            case "p_pg_ctr":
                cmdObj = new SqlCommand();
                strWhrCond = "page_id='" + dtaInputInfo.Rows[0]["page_id"].ToString() + "'";
                strSql = "Delete from [sy_pg_ctr] where " + strWhrCond;
                cmdObj.CommandText = strSql;
                arlReturn.Add(cmdObj);
                cmdObj = new SqlCommand();
                strWhrCond = "page_id='" + dtaInputInfo.Rows[0]["page_id"].ToString() + "'";
                strSql = "Delete from [sy_pg_evnt] where " + strWhrCond;
                cmdObj.CommandText = strSql;
                arlReturn.Add(cmdObj);
                break;
            case "p_pg_ctr_droplist": // Added by 'Imran Gani' on 26-Dec-2013, for droplist support.
                cmdObj = new SqlCommand();
                strWhrCond = "page_id= '" + dtaInputInfo.Rows[0]["page_id"].ToString() + "';";
                strSql = "Delete from [sy_pg_ctr_droplist] where " + strWhrCond;
                cmdObj.CommandText = strSql;
                arlReturn.Add(cmdObj);
                strSql = updateDroplistValuesInObj(strWhrCond, dtaInputInfo);
                if (strSql.Trim() != "")
                {
                    cmdObj = new SqlCommand();
                    cmdObj.CommandText = strSql.Trim();
                    arlReturn.Add(cmdObj);
                }
                break;

            case "p_pgs_mem":
                cmdObj = new SqlCommand();
                strWhrCond = "pageset_id='" + dtaInputInfo.Rows[0]["pageset_id"].ToString() + "'";
                strSql = "Delete from [sy_pgs_mem] where " + strWhrCond;
                cmdObj.CommandText = strSql;
                arlReturn.Add(cmdObj);
                break;
            case "p_pgs_srh":
                cmdObj = new SqlCommand();
                strWhrCond = "pageset_id='" + dtaInputInfo.Rows[0]["pageset_id"].ToString() + "'";
                strSql = "Delete from [sy_pgs_srh] where " + strWhrCond;
                cmdObj.CommandText = strSql;
                arlReturn.Add(cmdObj);

                cmdObj = new SqlCommand(); //Added By Dhanapal On DEC272010
                strWhrCond = "pageset_id='" + dtaInputInfo.Rows[0]["pageset_id"].ToString() + "'";
                strSql = "Delete from [sy_pgs_mode] where " + strWhrCond;
                cmdObj.CommandText = strSql;file:///D:\FinalApp\AppFrame\App_Themes\LightGreenTheme\
                arlReturn.Add(cmdObj);

                HDDatabase HDD = new HDDatabase();
                string strmodeid = "";
                strmodeid = HDD.GetColumnValue("select af_row_id  from sy_pgs_mode where pageset_id = '" + dtaInputInfo.Rows[0]["pageset_id"].ToString() + "' and mode = 'TV'");
                if (strmodeid != "")
                {
                    cmdObj = new SqlCommand();
                    strWhrCond = "mode_id ='" + strmodeid.ToString() + "'";
                    strSql = "Delete from [sy_pgs_mode_fields] where " + strWhrCond;
                    cmdObj.CommandText = strSql;
                    arlReturn.Add(cmdObj);
                }
                strmodeid = HDD.GetColumnValue("select af_row_id  from sy_pgs_mode where pageset_id = '" + dtaInputInfo.Rows[0]["pageset_id"].ToString() + "' and mode = 'CV'");
                if (strmodeid != "")
                {
                    cmdObj = new SqlCommand();
                    strWhrCond = "mode_id ='" + strmodeid.ToString() + "'";
                    strSql = "Delete from [sy_pgs_mode_fields] where " + strWhrCond;
                    cmdObj.CommandText = strSql;
                    arlReturn.Add(cmdObj);
                }
                break;

            /*case "p_pgs_mode":
                cmdObj = new SqlCommand();
                strWhrCond = "pageset_id='" + dtaInputInfo.Rows[0]["pageset_id"].ToString() + "'";
                strSql = "Delete from [sy_pgs_mode] where " + strWhrCond;
                cmdObj.CommandText = strSql;
                arlReturn.Add(cmdObj);
                cmdObj = new SqlCommand();
                strWhrCond = "mode_id='" + dtaInputInfo.Rows[0]["af_row_id"].ToString() + "'";
                strSql = "Delete from [sy_pgs_mode_fields] where " + strWhrCond;
                cmdObj.CommandText = strSql;
                arlReturn.Add(cmdObj);
                break;*/
        }
        InsertInput II = new InsertInput();
        ArrayList arlIns = II.InsertPage(droPage, droPageCtrl, dtaInputInfo);
        for (int i = 0; i < arlIns.Count; i++)
        {
            arlReturn.Add(arlIns[i]);
        }
        return arlReturn;
    }

    private void updateMasterKey(ref DataTable dtInputInfo, string Master_Field, string Key_Value)
    {
        foreach (DataRow dr in dtInputInfo.Rows)
            dr[Master_Field] = Key_Value;
    }

    private void getRelationUpdateCommands(ref DataTable PageInfo, ref DataTable InputInfo, ref ArrayList cmdArrayList)
    {
        HDDatabase HDD = new HDDatabase();
        DataTable dtRelation = HDD.GetDataTable("select * from sy_relation where is_active='1' and operations like '%update%'");
        PageInfoLoad PIL = new PageInfoLoad();
        DataRow[] drr = dtRelation.Select("source_table='" + PageInfo.Rows[0]["page_id"].ToString() + "'");
        foreach (DataRow dr in drr)
        {
            DataSet dsPageInfoTemp = PIL.GetPageSetPageCtrlInfo(HDD.GetColumnValue("select sy_pgs.af_row_id from sy_pgs, sy_pgs_mem where sy_pgs.af_row_id=sy_pgs_mem.pageset_id and sy_pgs_mem.page_id='" + dr["Relation_Table"].ToString() + "'"));
            string sKeyValue = getKeyValueForRelationTable(PageInfo.Rows[0]["page_table_name"].ToString().Replace("'", "''"), dr["Source_Field"].ToString(), "af_row_id", InputInfo.Rows[0]["af_row_id"].ToString(), ref HDD);
            string sTableName = HDD.GetColumnValue("Select page_table_name from sy_pg where page_id='" + dr["Relation_Table"].ToString() + "'");
            getRelationCommand(ref cmdArrayList, sTableName, dr["Relation_Field"].ToString(), dr["Source_Field"].ToString(), dr["condition"].ToString().Trim(), sKeyValue, InputInfo.Rows[0]);
            //String sSql = "select * from " + sTableName + " where " + dr["Relation_Field"].ToString() + "='" + sKeyValue + "' ";

            //if (dr["condition"].ToString().Trim() != "")
            //{
            //    sSql = sSql + "and " + dr["condition"].ToString();
            //}

            //if (HDD.GetDataTable(sSql).Rows.Count > 0)
            //{
            //    if (dr["Relation_Field"].ToString() != "af_row_id")
            //    {
            //        sKeyValue = HDD.GetColumnValue("select af_row_id from " + sTableName + " where " + dr["Relation_Field"].ToString().Replace("'", "''") + "='" + sKeyValue.Replace("'", "''") + "'");
            //    }

            //    if (sKeyValue != "")
            //    {
            //        DeleteChildRecords(ref cmdArrayList, dsPageInfoTemp.Tables["Page"], sKeyValue, HDD);
            //    }
            //}
        }
        PIL = null;
        drr = null;
        dtRelation.Dispose();
        dtRelation = null;
    }

    private void getRelationCommand(ref ArrayList cmdArrayList, String RelationTable, String RelationField,String SourceField, String Condition, String KeyValue, DataRow CurrentData)
    {
        String sSql = "";

        if (Condition == "")
        {
            //sSql = "delete from [" + RelationTable.Replace("'", "''") + "] where [" + RelationField.Replace("'", "''") + "]='" + KeyValue.Replace("'", "''") + "'";
            sSql = "update " + RelationTable.Replace("'", "''") + " set " + RelationField.Replace("'", "''") + "='" + CurrentData[SourceField].ToString().Replace("'", "''") + "' where " + RelationField.Replace("'", "''") + " = '" + KeyValue.Replace("'", "''") + "'";
        }
        else
        {
            //sSql = "delete from [" + RelationTable.Replace("'", "''") + "] where [" + RelationField.Replace("'", "''") + "]='" + KeyValue.Replace("'", "''") + "' and " + Condition;
            sSql = "update " + RelationTable.Replace("'", "''") + " set " + RelationField.Replace("'", "''") + "='" + CurrentData[SourceField].ToString().Replace("'", "''") + "' where " + RelationField.Replace("'", "''") + " = '" + KeyValue.Replace("'", "''") + "' and " + Condition;
        }

        SqlCommand cmdObj = new SqlCommand(sSql);
        cmdArrayList.Add(cmdObj);
    }

    private String getKeyValueForRelationTable(String SourceTableName, String SouceFieldName, String KeyField, String KeyValue, ref HDDatabase HDD)
    {
        string sReturn = "";
        try
        {
            //datatype is to be determined for quote identifier
            sReturn = HDD.GetColumnValue("select [" + SouceFieldName.Replace("'", "''") + "] from [" + SourceTableName.Replace("'", "''") + "] where  [" + KeyField.Replace("'", "''") + "]='" + KeyValue.Replace("'", "''") + "'");
        }
        catch
        {
        }
        finally
        {
        }
        return sReturn;
    }

    /// <summary>
    /// To replace the modified droplist values in real table.
    /// </summary>
    /// <param name="sWhereCnd">where condition for page_id.</param>
    /// <param name="dtaInputInfo">Input data table</param>
    /// <returns>Update Query</returns>
    // Added by 'Imran Gani' on 26-Dec-2013, for droplist support.
    private string updateDroplistValuesInObj(string sWhereCnd, DataTable dtaInputInfo)
    {
        string sReturnSQL = "";
        HDDatabase hd = new HDDatabase();
        DataTable dt_ExistsDroplist = new DataTable();
        try
        {
            if (dtaInputInfo.Rows.Count == 0) return sReturnSQL;
            string sExistingVals = string.Empty;
            //DataTable dt_NewDroplist = dtaInputInfo.Clone();
            dt_ExistsDroplist = hd.GetDataTable("select * from [sy_pg_ctr_droplist] where " + sWhereCnd);
            foreach (DataRow dr in dt_ExistsDroplist.Rows)
                sExistingVals += "'" + dr["droplist_value"].ToString() + "',";
            string sFldRowId = "", sFieldName = "";
            string strTableName = hd.GetColumnValue("select [page_table_name] from [sy_pg] where [af_row_id] = '" + dtaInputInfo.Rows[0]["page_id"].ToString() + "';");
            if (!string.IsNullOrEmpty(sExistingVals))
            {
                foreach (DataRow dr_new in dtaInputInfo.Select("droplist_value not in (" + sExistingVals.TrimEnd(',') + ")"))
                {
                    if (dr_new["droplist_value"].ToString().ToLower() == "none" || dr_new["droplist_value"].ToString().ToLower() == "empty") continue;
                    DataRow[] dr_exits = dt_ExistsDroplist.Select("control_id = '" + dr_new["control_id"].ToString() + "' and droplist_seq = '" + dr_new["droplist_seq"].ToString() + "'");
                    if (dr_exits.Length == 0) continue;
                    //if (dr_new["droplist_value"].ToString() == dr_exits[0]["droplist_value"].ToString()) continue;
                    if (sFldRowId != dr_new["control_id"].ToString())
                    {
                        sFieldName = hd.GetColumnValue("select [control_id] from [sy_pg_ctr] where [af_row_id] = '" + dr_new["control_id"].ToString() + "';");
                        sFldRowId = dr_new["control_id"].ToString();
                    }
                    sReturnSQL += "update [" + strTableName + "] set [" + sFieldName + "] = '" + dr_new["droplist_value"].ToString() + "' where [" + sFieldName + "] = '" + dr_exits[0]["droplist_value"].ToString() + "'; ";
                }
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error : updateDroplistValuesInObj :" + ex.Message);
        }
        finally
        {
            dt_ExistsDroplist.Dispose();
            hd = null;
        }
        return sReturnSQL;
    }
}